﻿// Type: GandA.Component.DataAccess.MySQL.MySQL
// Assembly: GandA.Component.DataAccess, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
// Assembly location: C:\CodePlex\CLab\CLab\DLLs\GandA.Component.DataAccess.dll

using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.Common;

namespace GandA.Component.DataAccess.MySQL
{
    public class MySQL
    {
        private static string sConnectionString = string.Empty;
        private static MySqlTransaction oTransaction;
        private static MySqlConnection oConnection;
        private static MySqlCommand oCommand;
        private static MySqlDataReader oDataReader;
        private static MySqlParameter oPr;

        static MySQL()
        {
        }

        public MySQL()
        {
            MySQL.sConnectionString = this.ConnectionString();
            MySQL.oTransaction = (MySqlTransaction)null;
            MySQL.oConnection = (MySqlConnection)null;
            MySQL.oCommand = (MySqlCommand)null;
            MySQL.oDataReader = (MySqlDataReader)null;
            MySQL.oPr = (MySqlParameter)null;
        }

        public MySqlDataReader ExecuteReaderProcedure(string pSPName)
        {
            return this.ExecuteReaderProcedure(pSPName, (MySQLParameterCollection)null);
        }

        public MySqlDataReader ExecuteReaderProcedure(string pSPName, MySQLParameterCollection pParameterCollection)
        {
            try
            {
                this.OpenConnection();
                MySQL.oCommand = new MySqlCommand(pSPName, MySQL.oConnection);
                MySQL.oCommand.CommandType = CommandType.StoredProcedure;
                if (pParameterCollection != null)
                {
                    foreach (MySQLParameter mySqlParameter in (CollectionBase)pParameterCollection)
                    {
                        MySQL.oPr = new MySqlParameter();
                        MySQL.oPr.ParameterName = mySqlParameter.ParamName;
                        MySQL.oPr.Value = mySqlParameter.ParamValue;
                        MySQL.oPr.Direction = (ParameterDirection)mySqlParameter.EnumParameterDirection;
                        MySQL.oPr.MySqlDbType = (MySqlDbType)mySqlParameter.EnumMySqlDbType;
                        MySQL.oCommand.Parameters.Add(MySQL.oPr);
                    }
                }
                MySQL.oDataReader = MySQL.oCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (MySqlException ex)
            {
                throw new MySQLException(ex.Message, (Exception)ex);
            }
            finally
            {
                if (MySQL.oCommand != null)
                {
                    MySQL.oCommand.Dispose();
                    MySQL.oCommand = (MySqlCommand)null;
                }
            }
            return MySQL.oDataReader;
        }

        public void ExecuteNonQueryStoreProcedure(string pSPName)
        {
            this.ExecuteNonQueryStoreProcedure(pSPName, (MySQLParameterCollection)null, true);
        }

        public void ExecuteNonQueryStoreProcedure(string pSPName, MySQLParameterCollection pParameterCollection)
        {
            this.ExecuteNonQueryStoreProcedure(pSPName, pParameterCollection, true);
        }

        public void ExecuteNonQueryStoreProcedure(string pSPName, bool isNormalTransaction)
        {
            this.ExecuteNonQueryStoreProcedure(pSPName, (MySQLParameterCollection)null, isNormalTransaction);
        }

        public void ExecuteNonQueryStoreProcedure(string pSPName, MySQLParameterCollection pParameterCollection, bool isNormalTransaction)
        {
            try
            {
                this.OpenConnection();
                if (MySQL.oTransaction == null && isNormalTransaction)
                    this.BeginTransaction();
                MySQL.oCommand = new MySqlCommand(pSPName, MySQL.oConnection, MySQL.oTransaction);
                MySQL.oCommand.CommandType = CommandType.StoredProcedure;
                if (pParameterCollection != null)
                {
                    foreach (MySQLParameter mySqlParameter in (CollectionBase)pParameterCollection)
                    {
                        MySQL.oPr = new MySqlParameter();
                        MySQL.oPr.ParameterName = mySqlParameter.ParamName;
                        MySQL.oPr.Value = mySqlParameter.ParamValue;
                        MySQL.oPr.Direction = (ParameterDirection)mySqlParameter.EnumParameterDirection;
                        MySQL.oPr.MySqlDbType = (MySqlDbType)mySqlParameter.EnumMySqlDbType;
                        MySQL.oCommand.Parameters.Add(MySQL.oPr);
                    }
                }
                MySQL.oCommand.ExecuteNonQuery();
                if (isNormalTransaction)
                    this.CommitTransaction();
                this.CloseConnection();
            }
            catch (MySqlException ex)
            {
                if (MySQL.oTransaction != null && isNormalTransaction)
                    this.RollbackTransaction();
                throw new MySQLException(ex.Message, (Exception)ex);
            }
            finally
            {
                this.CloseConnection();
                if (MySQL.oCommand != null)
                {
                    MySQL.oCommand.Dispose();
                    MySQL.oCommand = (MySqlCommand)null;
                }
            }
        }

        public DataSet ExecuteQueryStoreProcedure(string pSPName)
        {
            return this.ExecuteQueryStoreProcedure(pSPName, (MySQLParameterCollection)null, true, false);
        }

        public DataSet ExecuteQueryStoreProcedure(string pSPName, MySQLParameterCollection pParameterCollection)
        {
            return this.ExecuteQueryStoreProcedure(pSPName, pParameterCollection, true, false);
        }

        public void ExecuteQueryStoreProcedure(string pSPName, bool isNormalTransaction)
        {
            this.ExecuteQueryStoreProcedure(pSPName, (MySQLParameterCollection)null, isNormalTransaction, false);
        }

        public DataSet ExecuteQueryStoreProcedure(string pSPName, MySQLParameterCollection pParameterCollection, bool isNormalTransaction, bool pConsulta)
        {
            try
            {
                this.OpenConnection();
                if (!pConsulta)
                {
                    if (MySQL.oTransaction == null && isNormalTransaction)
                        this.BeginTransaction();
                    MySQL.oCommand = new MySqlCommand(pSPName, MySQL.oConnection, MySQL.oTransaction);
                }
                else
                    MySQL.oCommand = new MySqlCommand(pSPName, MySQL.oConnection);
                MySQL.oCommand.CommandType = CommandType.StoredProcedure;
                if (pParameterCollection != null)
                {
                    foreach (MySQLParameter mySqlParameter in (CollectionBase)pParameterCollection)
                    {
                        MySQL.oPr = new MySqlParameter();
                        MySQL.oPr.ParameterName = mySqlParameter.ParamName;
                        MySQL.oPr.Value = mySqlParameter.ParamValue;
                        MySQL.oPr.Direction = (ParameterDirection)mySqlParameter.EnumParameterDirection;
                        MySQL.oPr.MySqlDbType = (MySqlDbType)mySqlParameter.EnumMySqlDbType;
                        MySQL.oCommand.Parameters.Add(MySQL.oPr);
                    }
                }
                MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter();
                DataSet dataSet = new DataSet();
                mySqlDataAdapter.SelectCommand = MySQL.oCommand;
                ((DataAdapter)mySqlDataAdapter).Fill(dataSet);
                if (!pConsulta && isNormalTransaction)
                    this.CommitTransaction();
                this.CloseConnection();
                return dataSet;
            }
            catch (MySqlException ex)
            {
                if (MySQL.oTransaction != null && isNormalTransaction)
                    this.RollbackTransaction();
                throw new MySQLException(ex.Message, (Exception)ex);
            }
            finally
            {
                this.CloseConnection();
                if (MySQL.oCommand != null)
                {
                    MySQL.oCommand.Dispose();
                    MySQL.oCommand = (MySqlCommand)null;
                }
            }
        }

        public DataSet ExecuteQuerySP(string pSPName, MySQLParameterCollection pParameterCollection)
        {
            return this.ExecuteQueryStoreProcedure(pSPName, pParameterCollection, true, true);
        }

        public DataSet ExecuteQuerySP(string pSPName)
        {
            return this.ExecuteQueryStoreProcedure(pSPName, (MySQLParameterCollection)null, true, true);
        }

        public void OpenConnection()
        {
            MySQL.oConnection = new MySqlConnection(MySQL.sConnectionString);
            MySQL.oConnection.Open();
        }

        public void CloseConnection()
        {
            if (MySQL.oConnection.State != ConnectionState.Open)
                return;
            MySQL.oConnection.Close();
        }

        public void BeginTransaction()
        {
            MySQL.oTransaction = MySQL.oConnection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            MySQL.oTransaction.Commit();
            MySQL.oTransaction.Dispose();
            MySQL.oTransaction = (MySqlTransaction)null;
        }

        public void RollbackTransaction()
        {
            MySQL.oTransaction.Rollback();
            MySQL.oTransaction.Dispose();
            MySQL.oTransaction = (MySqlTransaction)null;
        }

        private string ConnectionString()
        {
            string str = "";
            ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["ConnectionString"];
            if (connectionStringSettings.ConnectionString != null)
                str = connectionStringSettings.ConnectionString;
            return str;
        }
    }
}
